import { In } from "typeorm";
import { AppDataSource } from './data-source';
import { User } from "./entity/User"
import { IdCard } from "./entity/IdCard";
import { Department } from "./entity/Department";
import { Employee } from "./entity/Employee";
import { Article } from "./entity/Article";
import { Tag } from "./entity/Tag";

async function addData(userInstance, userEntity, appDataSource) {
    userInstance.firstName = "Timber"
    userInstance.lastName = "Saw"
    userInstance.age = 25
    await appDataSource.manager.save(userInstance)
    await appDataSource.manager.save(userEntity, [
        { firstName: 'ccc', lastName: 'ccc', age: 21},
        { firstName: 'ddd', lastName: 'ddd', age: 22},
        { firstName: 'eee', lastName: 'eee', age: 23}
    ]);
}

async function updateData(userInstance, userEntity, appDataSource) {
    userInstance.id = 1
    userInstance.firstName = "Timber"
    userInstance.lastName = "Saw"
    userInstance.age = 25
    await appDataSource.manager.save(userInstance)
    await appDataSource.manager.save(userEntity, [
        { id: 1, firstName: 'ccc666', lastName: 'ccc888', age: 21},
        { id: 2, firstName: 'ddd666', lastName: 'ddd888', age: 22},
        { id: 3, firstName: 'eee666', lastName: 'eee888', age: 23}
    ]);
}

async function getData(userEntity, appDataSource) {
    const users1 = await appDataSource.manager.find(userEntity)
    const users2 = await appDataSource.manager.findBy(userEntity, { age: 23 });
    const [users3, count] = await appDataSource.manager.findAndCount(userEntity);
    const [users4, count1] = await appDataSource.manager.findAndCountBy(userEntity, { age: 23 })
    const user5 = await await appDataSource.manager.findOne(userEntity, {
        select: { firstName: true, age: true },
        where: { id: 4 },
        order: { age: 'ASC' }
    });
    const users6 = await await appDataSource.manager.find(userEntity, {
        select: { firstName: true, age: true },
        where: { id: In([4, 8]) },
        order: { age: 'ASC' }
    });
    const user8 = await appDataSource.manager.findOneBy(userEntity, { age: 23 });
    try {
        const user9 = await appDataSource.manager.findOneOrFail(userEntity, {
            where: { id: 666 }
        });
    }catch(e) {
        console.log('没找到该用户 => ', e);
    }
}

async function deleteData(userInstance, userEntity, appDataSource) {
    await appDataSource.manager.delete(userEntity, 1);
    await appDataSource.manager.delete(userEntity, [2,3]);

    userInstance.id = 1
    await appDataSource.manager.remove(userEntity, userInstance);
}

async function customSQL(userEntity, appDataSource) {
    // 简单SQL编写
    const users10 = await appDataSource.manager.query('select * from user where age in(?, ?)', [21, 22]);

    // 复杂SQL编写(复杂关联查询用queryBuilder、简单查询用find)
    const queryBuilder = await appDataSource.manager.createQueryBuilder();
    const user11 = await queryBuilder.select("user").from(userEntity, "user").where("user.age = :age", { age: 21 }).getOne();
}

async function openTransaction(userEntity, appDataSource) {
    // 多条关联数据开启事务
    await appDataSource.manager.transaction(async manager => {
        await manager.save(userEntity, {
            id: 4,
            firstName: 'eee',
            lastName: 'eee',
            age: 20
        });
    });
}

async function optimizeOperate(userEntity, appDataSource) {
    // 简化每次增删改查都需要传实体类
    const userEntitys = appDataSource.manager.getRepository(userEntity)
    const user12 = await userEntitys.find()
}

async function oneToOne(idCardEntity, userEntity, appDataSource) {
    const user = new User();
    user.id = 1;
    user.firstName = 'guang1111';
    user.lastName = 'guang1111';
    user.age = 20;
    const idCard = new IdCard();
    idCard.id = 1;
    idCard.cardName = '22222';
    idCard.user = user;
    // 关联更新(主表可以不用save, 从表也会跟着更新)
    await appDataSource.manager.save(user);
    await appDataSource.manager.save(idCard);
    // 数据查询(三种方式)
    const ics1 = await appDataSource.manager.find(idCardEntity, { relations: { user: true } });
    const userData = await appDataSource.manager.find(userEntity, { relations: { idCard: true } });
    const ics2 = await appDataSource.manager.getRepository(idCardEntity)
    .createQueryBuilder("ic").leftJoinAndSelect("ic.user", "u").getMany();
    const ics3 = await appDataSource.manager.createQueryBuilder(idCardEntity, "ic")
    .leftJoinAndSelect("ic.user", "u").getMany();
}

async function oneToMany(departmentEntity, employeeEntity, appDataSource) {
    const d1 = new Department();
    d1.name = '技术部';
    const e1 = new Employee();
    e1.name = '张三';
    e1.department = d1;
    const e2 = new Employee();
    e2.name = '李四';
    e2.department = d1;
    const e3 = new Employee();
    e3.name = '王五';
    e3.department = d1;
    // 关联更新(主表可以不用save, 从表也会跟着更新)
    appDataSource.manager.save(departmentEntity, d1);
    appDataSource.manager.save(employeeEntity, [e1, e2, e3]);
    // 数据查询(三种方式)
    const de1 = await appDataSource.manager.find(departmentEntity, {
        relations: { employees: true }
    });
    const de2 = await appDataSource.manager.getRepository(departmentEntity)
    .createQueryBuilder('d').leftJoinAndSelect('d.employees', 'e').getMany();
    const de3 = await appDataSource.manager.createQueryBuilder(departmentEntity, 'd')
    .leftJoinAndSelect('d.employees', 'e').getMany();
}

async function manyToMany(tagEntity, articleEntity, appDataSource) {
    const t1 = new Tag();
    t1.name = 'ttt1111';
    const t2 = new Tag();
    t2.name = 'ttt2222';
    const t3 = new Tag();
    t3.name = 'ttt33333';

    const a1 = new Article();
    a1.title = 'aaaa';
    a1.content = 'aaaaaaaaaa';
    a1.tags = [t1,t2];

    const a2 = new Article();
    a2.title = 'bbbbbb';
    a2.content = 'bbbbbbbbbb';
    a2.tags = [t1,t2,t3];

    // 关联更新(直接使用manager进行save保存)
    const entityManager = appDataSource.manager;
    await entityManager.save(t1);
    await entityManager.save(t2);
    await entityManager.save(t3);
    await entityManager.save(a1);
    await entityManager.save(a2);

    // 查询数据
    const article1 = await entityManager.find(articleEntity, { relations: { tags: true } });
    const article2 = await entityManager.createQueryBuilder(articleEntity, "a")
    .leftJoinAndSelect("a.tags", "t").getMany()
    const article3 = await entityManager.getRepository(articleEntity)
    .createQueryBuilder( "a").leftJoinAndSelect("a.tags", "t").getMany()

    // 更改数据(对应的中间表会自动更新)
    const article = await entityManager.findOne(articleEntity, {
        where: { id: 2 },
        relations: { tags: true }
    });
    article.title = "ccccc";
    article.tags = article.tags.filter(item => item.name.includes('ttt111'));
    await entityManager.save(article);

    // 删除数据(对应的中间表会自动更新)
    await entityManager.delete(articleEntity, 1);
    await entityManager.delete(tagEntity, 1);

    // 查询数据
    const tags = await entityManager.find(tagEntity, {
        relations: { articles: true }
    });
    console.log(tags);
}

AppDataSource.initialize().then(async () => {
    const user = new User();
    addData(user, User, AppDataSource);
    updateData(user, User, AppDataSource);
    getData(User, AppDataSource);
    deleteData(user, User, AppDataSource);
    customSQL(User, AppDataSource);
    openTransaction(User, AppDataSource);
    optimizeOperate(User, AppDataSource);
    oneToOne(IdCard, User, AppDataSource);
    oneToMany(Department, Employee, AppDataSource);
    manyToMany(Tag, Article, AppDataSource);
}).catch(error => console.log(error))
